using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SecurityLinkUp
{
    public class CommunityData
    {
        // Get the count of the number of communities for a given status (or all if status = any)
        public static int GetCommunityCount(CommonData.Status status)
        {
            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the (active) communities
                    SqlCommand cmd = new SqlCommand("GetCommunityCount", conn);
                    if (status == CommonData.Status.ANY)
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", status.ToString()));
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        return int.Parse(reader["count"].ToString());
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return 0;
            } 
        }

        // creates a community object for the supplied community ID
        public static Community GetCommunity(Guid CommunityID)
        {
            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the (active) communities
                    SqlCommand cmd = new SqlCommand("GetCommunityFromID", conn);
                    cmd.Parameters.Add(new SqlParameter("@ID", CommunityID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        Community ret = CreateCommunityFromDBRecord(reader);
                        return ret;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }

        // creates an arraylist of communities of the given status (or all if status = any)
        public static ArrayList GetCommunities(CommonData.Status status)
        {
            ArrayList ret = new ArrayList();

            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the communities based on their status
                    SqlCommand cmd = new SqlCommand("GetCommunities", conn);
                    if (status == CommonData.Status.ANY)
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", status.ToString()));
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        //create a community object and add it to the return array
                        Community c = CreateCommunityFromDBRecord(reader);
                        if (c != null)
                        {
                            ret.Add(c);
                        }
                    }
                    return ret;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            } 
        }

        // Gets the communities a user admins
        public static ArrayList GetUserAdminCommunities(Guid UserID)
        {
            ArrayList ret = new ArrayList();

            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("GetUserAdminCommunities", conn);
                    cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        //create a community object and add it to the return array
                        Community c = CreateCommunityFromDBRecord(reader);
                        ret.Add(c);
                    }
                    return ret;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }

        // gets all the admins for a communitiy
        public static SortedList GetCommunityAdmins(Guid CommunityID)
        {
            SortedList ret = new SortedList();

            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();

                    // setup the query to get all the chapters
                    SqlCommand cmd = new SqlCommand("GetCommunityAdmins", conn);
                    cmd.Parameters.Add(new SqlParameter("@CommunityID", CommunityID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        // get the userID
                        Guid UserID = new Guid(reader["userid"].ToString());

                        //create a user object and add it to the return array
                        User u = UserData.GetUser(UserID);
                        ret.Add(u.uid, u);
                    }
                    return ret;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }

        public static bool AddCommunityAdmin(Guid CommunityID, Guid UserID)
        {
            try
            {
                // check that the calling user has the permissions to do this operation
                MembershipUser currentusr = Membership.GetUser();
                if (IsCommunityAdminOrSuperAdmin(CommunityID, (Guid)currentusr.ProviderUserKey))
                {
                    string connstr = Security.GetDBConnectionString();
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand("AddCommunityAdmin", conn);
                        cmd.Parameters.Add(new SqlParameter("@CommunityID", CommunityID.ToString()));
                        cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();

                        // if the user isnt a chapteradmin already, add them to the role group
                        MembershipUser usr = Membership.GetUser(UserID);
                        if (!Roles.IsUserInRole(usr.UserName, "CommunityAdmin"))
                        {
                            Roles.AddUserToRole(usr.UserName, "CommunityAdmin");
                        }
                        return true;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }

        private static bool IsCommunityAdminOrSuperAdmin(Guid ChapterID, Guid UserID)
        {
            // get the user
            MembershipUser usr = Membership.GetUser(UserID);

            if (Roles.IsUserInRole(usr.UserName, "SuperAdmin"))
            {
                return true;
            }

            if (Roles.IsUserInRole("CommunityAdmin"))
            {
                // do DB query to see if user is in the chapter admin table
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("IsCommunityAdmin", conn);
                    cmd.Parameters.Add(new SqlParameter("@CommunityID", ChapterID));
                    cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            return false;
        }

        // removes a user as an admin from a community
        public static bool RemoveCommunityAdmin(Guid CommunityID, Guid UserID)
        {
            try
            {
                // check that the calling user has the permissions to do this operation
                MembershipUser currentusr = Membership.GetUser();
                if (IsCommunityAdminOrSuperAdmin(CommunityID, (Guid)currentusr.ProviderUserKey))
                {
                    string connstr = Security.GetDBConnectionString();
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand("RemoveCommunityAdmin", conn);
                        cmd.Parameters.Add(new SqlParameter("@CommunityID", CommunityID));
                        cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();

                        // Is the user an admin of any other chapters? If they arent,
                        // remove their admin role
                        ArrayList chapters = GetUserAdminCommunities(UserID);
                        if (chapters.Count == 0)
                        {
                            MembershipUser usr = Membership.GetUser(UserID);
                            Roles.RemoveUserFromRole(usr.UserName, "ChapterAdmin");
                        }

                        return true;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }

        // deletes a community
        public static bool DeleteCommunity(Guid CommunityID)
        {
            try
            {

                if (Roles.IsUserInRole("SuperAdmin"))
                {
                    string connstr = Security.GetDBConnectionString();
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand("DeleteCommunity", conn);
                        cmd.Parameters.Add(new SqlParameter("@ID", CommunityID.ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();
                        return true;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }

        #region Get/Save chapter object from/to database
        // TODO: we could probably do with limiting the scope of these functions so that
        // they cant be called directly, and you have to go through the main operation
        // functions above.  However, because we are calling these functions static, we can't
        // move them from "public"?

        // Creates a communit object from a database record
        public static Community CreateCommunityFromDBRecord(SqlDataReader data)
        {
            //create a community object
            try
            {
                // create community using the "no position" method
                Community c = new Community(new Guid(data["uid"].ToString()),
                        data["name"].ToString(),
                        data["description"].ToString(),
                        (CommonData.Status)Enum.Parse(typeof(CommonData.Status),data["status"].ToString()),
                        data["statusnotes"].ToString()
                        );
                return c;
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }

        // saves a community object to the database
        public static string SaveCommunityToDB(Community c)
        {
            string connstr = Security.GetDBConnectionString();
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                try
                {
                    conn.Open();
                    // check to see if the chaptername is unique if it's "new"
                    if (c.uid == Guid.Empty)
                    {
                        SqlCommand checkcmd = new SqlCommand("CheckUniqueCommunityName", conn);
                        checkcmd.Parameters.Add(new SqlParameter("@name", c.name));
                        checkcmd.CommandType = CommandType.StoredProcedure;
                        SqlDataReader checkreader = checkcmd.ExecuteReader();
                        checkreader.Read();
                        if (checkreader["result"].ToString() != "UNIQUE")
                        {
                            return "Community name is not unique";
                        }
                        checkreader.Close();
                    }

                    // save the community
                    SqlCommand cmd = new SqlCommand("SaveCommunity", conn);
                    if (c.uid != Guid.Empty)
                    {
                        cmd.Parameters.Add(new SqlParameter("@ID", c.uid.ToString()));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@ID", DBNull.Value));
                    }
                    cmd.Parameters.Add(new SqlParameter("@name", c.name));
                    cmd.Parameters.Add(new SqlParameter("@description", c.description));
                    cmd.Parameters.Add(new SqlParameter("@status", c.status.ToString()));
                    cmd.Parameters.Add(new SqlParameter("@statusnotes", c.statusnotes));
                    cmd.Parameters.Add(new SqlParameter("@CreatedBy", Membership.GetUser().ProviderUserKey));
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                    return "";
                }
                catch (Exception ex)
                {
                    Log.LogException(ex);
                    return "Error saving";
                }
            } 
        }
        #endregion

    }


    public class Community
    {
        private Guid m_uid;
        private string m_name;
        private string m_description;
        private CommonData.Status m_status;
        private string m_statusnotes;

        public Community(Guid uid, string name, string description, CommonData.Status status, string statusnotes)
        {
            m_uid = uid;
            m_name = name;
            m_description = description;
            m_status = status;
            m_statusnotes = statusnotes;
        }

        public Guid uid
        {
            get { return m_uid; }
        }

        public string name
        {
            get { return m_name; }
            set { m_name = value; }
        }

        public string description
        {
            get { return m_description; }
            set { m_description = value; }
        }

        public CommonData.Status status
        {
            get { return m_status; }
            set { m_status = value; }
        }

        public string statusnotes
        {
            get { return m_statusnotes; }
            set { m_statusnotes = value; }
        }

    }
}
